﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SQLite;

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

         SQLiteConnection conn;
         SQLiteDataAdapter sda;
         DataSet ds;

        private void Form2_Load(object sender, EventArgs e)
        {
            string conString = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
            conn = new SQLiteConnection(conString);
            conn.Open();
            string ssql = "select cid from test_customer";
            SQLiteCommand cmd = new SQLiteCommand(ssql,conn);
            SQLiteDataReader sdr = cmd.ExecuteReader();
            AutoCompleteStringCollection acc = new AutoCompleteStringCollection();
            while (sdr.Read())
            {
                acc.Add(sdr[0].ToString());
            }
            conn.Close();
            textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
            textBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            textBox1.AutoCompleteCustomSource = acc;

        }

        private void button1_Click(object sender, EventArgs e)
        {
            SQLiteCommand cmd = new SQLiteCommand("select * from test_borrow", conn);
            sda = new SQLiteDataAdapter();
            sda.SelectCommand = cmd;
            ds = new DataSet();
            sda.Fill(ds, "test1");
            dataGridView1.DataSource = ds.Tables[0];
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string cid = textBox1.Text.ToString();
            string isbn = textBox2.Text;
            string cdate = dateTimePicker1.Text;
            string tag = comboBox1.Text;
            string instring = "insert into test_borrow (cid,isbn,cdate,tag) values (" + cid + ",'" + isbn + "',date('" + cdate + "'),'" + tag + "')";
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(instring, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            sda.Fill(ds, "test1");
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            textBox1.Text = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();
            textBox2.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
            dateTimePicker1.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
            comboBox1.Text = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string cid = textBox1.Text.ToString();
            string isbn = textBox2.Text;
            string cdate = dateTimePicker1.Text;
            string tag = comboBox1.Text;
            string upstring = "update test_borrow set cid =" + cid + ", isbn ='" + isbn + "', cdate =date('" + cdate + "'), tag ='" + tag + "' where cid = " + cid;
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(upstring, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            sda.Fill(ds, "test1");
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string cid = textBox1.Text.ToString();
            string destring = "delete from test_borrow where cid =" + cid;
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(destring, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            sda.Fill(ds, "test1");
        }
    }
}
